package com.ysstech.regression.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ysstech.common.entity.PageBean;
import com.ysstech.common.exception.BusinessException;
import com.ysstech.common.util.DateUtil;
import com.ysstech.common.util.RestTemplateUtil;
import com.ysstech.common.util.resultutil.RestResult;
import com.ysstech.common.util.resultutil.ResultGenerator;
import com.ysstech.regression.entity.Usecase;
import com.ysstech.regression.mapper.UsecaseMapper;
import com.ysstech.regression.service.UsecaseService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.*;
import java.text.DecimalFormat;
import java.util.*;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author lishuangliang
 * @since 2022-03-04
 */
@Service("usecaseService")
public class UsecaseServiceImpl extends ServiceImpl<UsecaseMapper, Usecase> implements UsecaseService {
    @Autowired
    protected RestTemplateUtil restTemplateUtil;
    @Autowired
    private DataSource dataSource;

    public PageBean<Usecase> queryAllUsecase(Usecase usecase, PageBean pageBean) throws Exception {
        QueryWrapper<Usecase> wrapper = this.getQueryWrapper(usecase);
        IPage pageDicvalue = this.page(new Page<>(pageBean.getPageNum(), pageBean.getPageSize()), wrapper);
        this.switchedListUsecase(pageDicvalue.getRecords());
        return new PageBean<>(pageDicvalue);
    }


    /**
     * 类型，机构等中文的转换
     *
     * @param list
     * @return
     * @throws Exception
     */
    public List<Usecase> switchedListUsecase(List<Usecase> list) throws Exception {
        return list;
    }

    /**
     * 查询条件
     *
     * @param usecase
     * @return
     * @throws Exception
     */
    private QueryWrapper<Usecase> getQueryWrapper(Usecase usecase) throws Exception {
        QueryWrapper<Usecase> wrapper = new QueryWrapper<>();
        if (null != usecase && StringUtils.isNotBlank(usecase.getSelName())) {
            wrapper.like("NAME", usecase.getSelName());
        }
        wrapper.orderByDesc("ADD_TIME");
        if (null == usecase) {
            return wrapper;
        }
        return wrapper;
    }


    public String sendGet(Usecase usecase) throws Exception {
        ResponseEntity<String> stringResponseEntity = restTemplateUtil.get(usecase.getUrl(), String.class);
        return stringResponseEntity.getBody();
    }

    /**
     * 定时任务调用接口
     *
     * @param id
     * @return
     * @throws Exception
     */
    public String scheduleUseCase(String id) throws Exception {
        if (StringUtils.isBlank(id)) {
            log.warn("定时任务没有获取到id");
            return null;
        }
        Usecase usecase = this.getById(id);
        Map<String, String> map = new HashMap<>();
        map.put("fsetcode", usecase.getFsetcode());
        map.put("fywdate", usecase.getFywdate());
        map.put("ftype", usecase.getFtype());
        map.put("commonparameters", usecase.getCommonparameters());
        map.put("fsetcodeparameters", usecase.getFsetcodeparameters());
        ResponseEntity<String> stringResponseEntity =
                restTemplateUtil.post(usecase.getUrl(), map, String.class);
        this.check(usecase);
        return stringResponseEntity.getBody();
    }

    public String sendPost(Usecase usecase) throws Exception {
        Map<String, String> map = new HashMap<>();
        map.put("fsetcode", usecase.getFsetcode());
        map.put("fywdate", usecase.getFywdate());
        map.put("ftype", usecase.getFtype());
        map.put("commonparameters", usecase.getCommonparameters());
        map.put("fsetcodeparameters", usecase.getFsetcodeparameters());
        ResponseEntity<String> stringResponseEntity =
                restTemplateUtil.post(usecase.getUrl(), map, String.class);
        this.check(usecase);
        return stringResponseEntity.getBody();
    }

    /**
     * 导入标准数据
     *
     * @param usecase
     * @return
     * @throws Exception
     */
    public Integer upload(Usecase usecase) throws Exception {
        return this.insert(this.getConnectionTest(), this.getConnectionBz(), getSql(usecase, ""));

    }

    /**
     * 通过sql查询数据
     *
     * @param usecase
     * @param pageBean
     * @return
     * @throws Exception
     */
    public RestResult queryData(Usecase usecase, PageBean pageBean) throws Exception {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            if ("1".equals(usecase.getFtype())) {
                usecase.setFtype("hzjkqs");
            }
            if ("2".equals(usecase.getFtype())) {
                usecase.setFtype("jjhzgzb");
            }
            if ("3".equals(usecase.getFtype())) {
                usecase.setFtype("fcwvch");
            }
            String sql = getSql3(usecase);
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            Map<String, Object> map = new HashMap<>();
            connection = this.getConnectionBz();
            Map<String, String> columnNames = this.getTableColumnNames(sql, connection);
            pStemt = connection.prepareStatement(sql /*+ "  limit " +
                    (pageBean.getPageNum() - 1) * pageBean.getPageSize() + "," + pageBean.getPageSize()*/);
            rs = pStemt.executeQuery();
            List<Object> list = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> stringMap = new HashMap<>();
                int a = 1;
                for (Map.Entry<String, String> info : columnNames.entrySet()) {
                    String colum = info.getKey();
                    stringMap.put(colum.toLowerCase(), rs.getString(a));
                    a++;
                    continue;
                }
                list.add(stringMap);
            }
            map.put("rows", list);
            pStemt = connection.prepareStatement("select count(1) as count from (" + sql + ")");
            ResultSet rs1 = pStemt.executeQuery();
            if (rs1.next()) {
                map.put("total", rs1.getBigDecimal("count"));
            }
            return ResultGenerator.getSuccessResult("查询数据成功", map);
        } catch (Exception ex) {
            throw new BusinessException("查询数据失败！" + ex.getMessage());
        } finally {
            rs.close();
            pStemt.close();
            connection.close();
        }
    }

    /**
     * 获取数据库表的 字段和对应的类型
     *
     * @param usecase
     * @return
     * @throws Exception
     */
    public Map<String, String> getTableColumnNames(Usecase usecase) throws Exception {
        Map<String, String> map = null;
        Connection conn = null;
        try {
            if ("1".equals(usecase.getFtype())) {
                usecase.setFtype("hzjkqs");
            }
            if ("2".equals(usecase.getFtype())) {
                usecase.setFtype("jjhzgzb");
            }
            if ("3".equals(usecase.getFtype())) {
                usecase.setFtype("fcwvch");
            }
            String sql = getSql3(usecase);
            conn = this.getConnectionBz();
            map = getTableColumnNames(sql, this.getConnectionBz());
        } catch (Exception ex) {
            throw new BusinessException("获取查询语句字段出错！");
        } finally {
            conn.close();
        }
        return map;
    }

    private String getSql3(Usecase usecase) throws Exception {
        String set = Integer.valueOf(usecase.getFsetcode()) > 999 ? (new DecimalFormat("000000"))
                .format(new BigDecimal(usecase.getFsetcode())) : (new DecimalFormat("000"))
                .format(new BigDecimal(usecase.getFsetcode()));
        String year = DateUtil.getSysYear(DateUtil.strToDate(usecase.getFywdate()));
        switch (usecase.getFtype()) {
            case "hzjkqs":
                return " select a.* from A" + set + "hzjkqs a where not exists(" +
                        " select * from A" + set + "hzjkqs_bz b where a.fdate=b.fdate " +
                        " and a.fszsh=b.fszsh and a.fywbz=b.fywbz and a.fbjsf=b.fbjsf" +
                        " ) and a.fdate=date'" + usecase.getFywdate() + "'";
            case "jjhzgzb":
                return " select a.* from A" + set + "jjhzgzb a where not exists(" +
                        " select * from A" + set + "jjhzgzb_bz b where a.fdate=b.fdate " +
                        " and a.fkmbm=b.fkmbm and a.fhqjg=b.fhqjg and a.fzqcb=b.fzqcb" +
                        " ) and a.fdate=date'" + usecase.getFywdate() + "'";
            case "fcwvch":
                return " select a.* from A" + year + set + "fcwvch_bz a where not exists(" +
                        " select * from A" + year + set + "fcwvch b where a.fdate=b.fdate " +
                        " and a.fkmh=b.fkmh and a.fbal=b.fbal and a.fbbal=b.fbbal" +
                        " ) and a.fdate=date'" + usecase.getFywdate() + "'";
        }
        return null;
    }

    /**
     * 查询数据
     *
     * @param usecase
     * @return
     * @throws Exception
     */
    public boolean queryData(Usecase usecase) throws Exception {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        boolean falg = false;
        try {
            String sql = getSql3(usecase);
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            connection = this.getConnectionBz();
            pStemt = connection.prepareStatement(sql);
            rs = pStemt.executeQuery();
            while (rs.next()) {
                falg = true;
                break;
            }
        } catch (Exception ex) {
            throw new BusinessException("查询数据失败！" + ex.getMessage());
        } finally {
            rs.close();
            pStemt.close();
            connection.close();
        }
        return falg;
    }

    /**
     * 比对测试库和标准库数据差异信息
     *
     * @param usecase
     * @return
     * @throws Exception
     */

    private String check(Usecase usecase) throws Exception {
        PreparedStatement pStemtTest = null;
        Connection conTest = this.getConnectionTest();
        Connection conBz = this.getConnectionBz();
        try {
            if ("1".equals(usecase.getFtype())) {
                usecase.setFtype("hzjkqs");
            }
            if ("2".equals(usecase.getFtype())) {
                usecase.setFtype("jjhzgzb");
            }
            if ("3".equals(usecase.getFtype())) {
                usecase.setFtype("fcwvch");
            }
            List<String> listTest = getSql(usecase, "_bz");
            if (CollectionUtils.isEmpty(listTest)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            if (tableExist(conBz, listTest.get(3))) { // 表存在
                // 删除表
                pStemtTest = conBz.prepareStatement(listTest.get(4));
                pStemtTest.execute();
            }
            // 执行创建表的语句
            pStemtTest = conBz.prepareStatement(listTest.get(5));
            pStemtTest.execute();
            this.insert(conTest, conBz, listTest);
            UpdateWrapper<Usecase> updateWrapper = new UpdateWrapper<>();
            updateWrapper.eq("ID", usecase.getId());
            Usecase usecase1 = new Usecase();
            if (queryData(usecase)) { // 查询有差异，更新字段有差异
                usecase1.setResult("核对有差异");
            } else {
                usecase1.setResult("核对一致");
            }
            this.update(usecase1, updateWrapper);
        } catch (Exception ex) {
            throw new BusinessException("核对数据失败，请稍后重试！");
        } finally {
            pStemtTest.close();
            conTest.close();
            conBz.close();
        }
        return null;
    }


    private boolean tableExist(Connection connection, String tableName) throws Exception {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select table_name from user_tables where table_name=?");
            ps.setString(1, tableName.toUpperCase());
            rs = ps.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (Exception ex) {
            throw new BusinessException("判断表时候存在出错");
        } finally {
            ps.close();
            rs.close();
        }
        return false;
    }

    /**
     * 通过时间类型获取对应的语句信息
     *
     * @param usecase
     * @return
     * @throws Exception
     */
    private List<String> getSql(Usecase usecase, String ftype) throws Exception {
        if (null == usecase || StringUtils.isBlank(usecase.getFsetcode())
                || StringUtils.isBlank(usecase.getFywdate())) {
            throw new BusinessException("获取标准数据信息失败，请稍后重试！");
        }
        List<String> list = new ArrayList<>();
        String set = Integer.valueOf(usecase.getFsetcode()) > 999 ? (new DecimalFormat("000000"))
                .format(new BigDecimal(usecase.getFsetcode())) : (new DecimalFormat("000"))
                .format(new BigDecimal(usecase.getFsetcode()));
        String year = DateUtil.getSysYear(DateUtil.strToDate(usecase.getFywdate()));
        switch (usecase.getFtype()) {
            case "hzjkqs":
                list.add("select * from A" + set + "hzjkqs where fdate=date'" + usecase.getFywdate() + "'");
                list.add("delete  from A" + set + "hzjkqs" + ftype + " where fdate=date'" + usecase.getFywdate() + "'");
                list.add("insert into A" + set + "hzjkqs" + ftype);

                list.add("A" + set + "hzjkqs" + ftype);
                list.add("drop table A" + set + "hzjkqs" + ftype);
                list.add("create table A" + set + "hzjkqs" + ftype + " as select * from A" + set + "hzjkqs where 1=2");
                return list;
            case "jjhzgzb":
                list.add("select * from A" + set + "jjhzgzb where fdate=date'" + usecase.getFywdate() + "'");
                list.add("delete  from A" + set + "jjhzgzb" + ftype + " where fdate=date'" + usecase.getFywdate() + "'");
                list.add("insert into A" + set + "jjhzgzb" + ftype);

                list.add("A" + set + "jjhzgzb" + ftype);
                list.add("drop table A" + set + "jjhzgzb" + ftype);
                list.add("create table A" + set + "jjhzgzb" + ftype + " as select * from A" + set + "jjhzgzb where 1=2");
                return list;
            case "fcwvch":
                list.add("select * from A" + year + set + "fcwvch where fdate=date'" + usecase.getFywdate() + "'");
                list.add("delete  from A" + year + set + "fcwvch" + ftype + " where fdate=date'" + usecase.getFywdate() + "'");
                list.add("insert into A" + year + set + "fcwvch" + ftype);

                list.add("A" + year + set + "fcwvch" + ftype);
                list.add("drop table A" + year + set + "fcwvch" + ftype);
                list.add("create table A" + year + set + "fcwvch" + ftype + " as select * from A" + year + set + "fcwvch where 1=2");
                return list;
        }
        return null;
    }

    /**
     * 将一个库的数据插入另一个数据库
     *
     * @return
     * @throws Exception
     */

    private Integer insert(Connection query, Connection insert, List<String> listsql) throws Exception {
        PreparedStatement pStemtQuery = null;
        PreparedStatement pStemtInsert = null;
        PreparedStatement pStemtDelete = null;
        ResultSet rs = null;
        int num = 0;
        try {
            if (CollectionUtils.isEmpty(listsql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            // 查询数据测试库当天数据
            Map<String, String> columnNames = this.getTableColumnNames(listsql.get(0), query);
            pStemtQuery = query.prepareStatement(listsql.get(0));
            rs = pStemtQuery.executeQuery();
            // 删除当天标准库的数据
            pStemtDelete = insert.prepareStatement(listsql.get(1));
            pStemtDelete.execute();
            // 拼接插入语句，插入标准库
            StringBuffer filed = new StringBuffer();
            filed.append("(");
            StringBuffer values = new StringBuffer();
            values.append("(");
            for (Map.Entry<String, String> info : columnNames.entrySet()) {
                filed.append(info.getKey()).append(",");
                values.append("?,");
            }
            filed.deleteCharAt(filed.length() - 1);    // 去掉最后一个逗号
            values.deleteCharAt(values.length() - 1);    // 去掉最后一个逗号
            filed.append(")");
            values.append(")");
            String sqlinsert = listsql.get(2) + filed.toString() + " values" + values.toString();
            pStemtInsert = insert.prepareStatement(sqlinsert);
            while (rs.next()) {
                int a = 1;
                for (Map.Entry<String, String> info : columnNames.entrySet()) {
                    if (info.getValue().startsWith("VARCHAR")) {
                        pStemtInsert.setString(a, rs.getString(info.getKey()));
                    }
                    if (info.getValue().startsWith("NUMBER")) {
                        pStemtInsert.setBigDecimal(a, rs.getBigDecimal(info.getKey()));
                    }
                    if (info.getValue().startsWith("DATE")) {
                        pStemtInsert.setDate(a, rs.getDate(info.getKey()));
                    }
                    a++;
                }
                pStemtInsert.addBatch();
                num++;
            }
            pStemtInsert.executeBatch();
        } catch (Exception ex) {
            throw new BusinessException("查询数据失败！" + ex.getMessage());
        } finally {
            rs.close();
            pStemtQuery.close();
            pStemtInsert.close();
            pStemtDelete.close();
            query.close();
            insert.close();
        }
        return num;
    }


    private Connection getConnectionTest() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        return DriverManager.getConnection("jdbc:oracle:thin:@192.168.32.217:1521:orcl", "fundacc", "fundacc");
        //return dataSource.getConnectionTest();
    }

    private Connection getConnectionBz() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        return DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "fundabc", "fundabc");
        //return dataSource.getConnectionTest();
    }

    /**
     * 获取数据库表的 字段和对应的类型
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public Map<String, String> getTableColumnNames(String sql, Connection connection) throws Exception {
        PreparedStatement pStemt = null;
        Map<String, String> columnNames = new LinkedHashMap<>();
        Map<String, Integer> columnCount = new HashMap<>();
        try {
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            pStemt = connection.prepareStatement(sql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                if (columnNames.containsKey(rsmd.getColumnName(i + 1))) {
                    int count = columnCount.get(rsmd.getColumnName(i + 1)) + 1;
                    columnNames.put(rsmd.getColumnName(i + 1) + count, rsmd.getColumnTypeName(i + 1));
                    columnCount.put(rsmd.getColumnName(i + 1), count);
                } else {
                    columnNames.put(rsmd.getColumnName(i + 1), rsmd.getColumnTypeName(i + 1));
                    columnCount.put(rsmd.getColumnName(i + 1), 0);
                }
            }
            if (null == columnNames || columnNames.size() == 0) {
                throw new BusinessException("没有获取到表" + sql + "字段和字段类型！");
            }
        } catch (Exception ex) {
            log.error("获取表" + sql + "字段和字段类型出错出错： " + ex);
            throw new BusinessException("获取表" + sql + "字段和字段类型出错出错： " + ex);
        } finally {
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
        }
        return columnNames;
    }




}
